﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace RMS.DB
{
    class LooseStoneMng : DBController
    {
        public List<LooseStone> GetAllItems()
        {
            var listItems = new List<LooseStone>();
            try
            {
                IOrderedQueryable<LooseStone> query = from item in DataEntities.LooseStones                                                      
                                                      orderby item.CertDate
                                                      select item;

                listItems = query.ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
            }

            return listItems;
        }
        public List<string> GetAllItemsId()
        {
            var items = GetAllItems();
            if (items.Count == 0) return null;
            return items.Select(item => item.CertNo).ToList();
        }
        public List<LooseStone> GetItemByElements(string id, string stonetype, string color)
        {
            var allItem = new List<LooseStone>();
            try
            {
                IQueryable<LooseStone> query = from item in DataEntities.LooseStones
                                              select item;
                allItem = query.ToList();

                if (!string.IsNullOrEmpty(id))
                {
                    IEnumerable<LooseStone> query1 = from item in allItem
                                                    where item.CertNo.ToLower().Contains(id.ToLower())
                                                    select item;
                    allItem = query1.ToList();
                }

                if (!string.IsNullOrEmpty(stonetype))
                {
                    IEnumerable<LooseStone> query2 = from item in allItem
                                                    where item.StoneType.ToLower().Contains(stonetype.ToLower())
                                                    select item;
                    allItem = query2.ToList();
                }
                if (!string.IsNullOrEmpty(color))
                {
                    IEnumerable<LooseStone> query3 = from item in allItem
                                                    where item.Color.ToLower().Contains(color.ToLower())
                                                    select item;
                    allItem = query3.ToList();
                }
            }
            catch (Exception)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
            }

            return allItem;
        }

        public List<LooseStone> GetAvailItemByElements(string id, string stonetype, string color)
        {
            var allItem = new List<LooseStone>();
            try
            {
                IQueryable<LooseStone> query = from item in DataEntities.LooseStones
                                               where item.Quantity > 0
                                               select item;
                allItem = query.ToList();

                if (!string.IsNullOrEmpty(id))
                {
                    IEnumerable<LooseStone> query1 = from item in allItem
                                                     where item.CertNo.ToLower().Contains(id.ToLower())
                                                     select item;
                    allItem = query1.ToList();
                }

                if (!string.IsNullOrEmpty(stonetype))
                {
                    IEnumerable<LooseStone> query2 = from item in allItem
                                                     where item.Category.ToLower().Contains(stonetype.ToLower())
                                                     select item;
                    allItem = query2.ToList();
                }
                if (!string.IsNullOrEmpty(color))
                {
                    IEnumerable<LooseStone> query3 = from item in allItem
                                                     where item.Color.ToLower().Contains(color.ToLower())
                                                     select item;
                    allItem = query3.ToList();
                }
            }
            catch (Exception)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
            }

            return allItem;
        }

        public List<LooseStone> GetRemainInStock()
        {
            var listItems = new List<LooseStone>();
            try
            {
                IOrderedQueryable<LooseStone> query = from item in DataEntities.LooseStones
                                                     where item.Quantity > 0
                                                     orderby item.Quantity
                                                     select item;

                listItems = query.ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
            }

            return listItems;
        }
        public bool AddNewItem(string certNo, string desc, int quantity, decimal importPrice, string stoneType, string certType, DateTime certDate, string shape,string color,string clarity,string cut,decimal length,decimal width,decimal depthPerHeight,decimal avgWidth,decimal depth,decimal weight,decimal table,string cate)
        {
            if (string.IsNullOrEmpty(certNo))
            {
                MessageBox.Show(Constants.Msg.FieldRequired);
                return false;
            }
            try
            {
                var item = new LooseStone()
                {
                    CertNo = certNo,
                    Description = desc,
                    Quantity = 1,
                    ImportPrice = importPrice,
                    StoneType = stoneType,
                    CertType = certType,
                    CertDate = certDate,
                    Shape = shape,
                    Color = color,
                    Clarity = clarity,
                    Cut = cut,
                    Length = length,
                    Width = width,
                    DepthHeight = depthPerHeight,
                    AvgWidth = avgWidth,
                    Depth = depth,
                    Weight = weight,
                    Table = table,
                    Category = cate
                };
                if (IsExist(item))
                {
                    MessageBox.Show(Constants.Msg.ItemStoneExist,"Chú ý");
                    return false;
                }
                DataEntities.AddToLooseStones(item);
                DataEntities.SaveChanges();
                return true;
            }
            catch (Exception)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
                return false;
            }
        }
        public LooseStone GetItemByElements(string certNo)
        {
            var _item = new LooseStone();
            try
            {
                IQueryable<LooseStone> query = from item in DataEntities.LooseStones
                                               where item.CertNo == certNo
                                               select item;

                _item = query.FirstOrDefault();
            }
            catch (Exception)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
                return null;
            }

            return _item;
        }
        public bool DeleteItem(string certNo)
        {
            try
            {
                var item = GetItemByElements(certNo);
                if(item != null){
                DataEntities.DeleteObject(item);
                DataEntities.SaveChanges();
                return true;
                }                
                return false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
                return false;
            }
        }
        public bool UpdateItem(string certNo, string desc, int quantity, decimal importPrice, string stoneType, string certType, DateTime certDate, string shape, string color, string clarity, string cut, decimal length, decimal width, decimal depthPerHeight, decimal avgWidth, decimal depth, decimal weight, decimal table,string cate)
        {
            try
            {
                LooseStone item = GetItemByElements(certNo);
                if (item == null)
                {
                    MessageBox.Show(Constants.Msg.NotItemExist);
                    return false;
                }
                item.AvgWidth = avgWidth;
                item.CertDate = certDate;
                item.CertType = certType;
                item.Clarity = clarity;
                item.Color = color;
                item.Cut = cut;
                item.Depth = depth;
                item.DepthHeight = depthPerHeight;
                item.Description = desc;
                item.ImportPrice = importPrice;
                item.Length = length;
                //item.Quantity = quantity;
                item.Shape = shape;
                item.StoneType = stoneType;
                item.Table = table;
                item.Weight = weight;
                item.Width = width;
                item.Category = cate;
                DataEntities.SaveChanges();
                return true;
            }
            catch (Exception)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
                return false;
            }
        }

        public bool UpdateItem(string certNo)
        {
            try
            {
                var inv = new LooseStone();
                inv = GetItemByElements(certNo);
                if (inv == null)
                {
                    MessageBox.Show(Constants.Msg.NotItemExist);
                    return false;
                }
                inv.Quantity = 0;
                inv.Weight = 0;
                DataEntities.SaveChanges();
                return true;
            }
            catch (Exception)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
                return false;
            }
        }
        public bool IsExist(LooseStone input)
        {
            if (input == null) return false;
            var itemId = input.CertNo;
            try
            {
                var output = DataEntities.LooseStones.Where(a => a.CertNo.ToLower() == itemId.ToLower());
                return output != null && output.Count() > 0 ? true : false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(Constants.Msg.ConnectError);
            }

            return false;
        }
    }
}
